﻿/*************************************************************************************
 *
 * 文 件 名:   InitXmlToDB
 * 描    述: 
 * 
 * 版    本：  V1.0
 * 创 建 者：  YSW 
 * 创建时间：  2021-09-24 8:29:15
 * ======================================
 * 历史更新记录
 * 版本：V          修改时间：         修改人：
 * 修改内容：
 * ======================================
*************************************************************************************/

using CommonModel.Field;
using GXRTBTC.PublicClassLibrary.Field;
using Microsoft.Extensions.Logging;
using PtlSvr.CfgLoader;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GXRTBTC.PublicClassLibrary.Util
{
    public class InitXmlToDB
    {
        /// <summary>
        /// 保存xml配置到数据库
        /// *************************更新说明*************************
        /// 20221104 ysw 新增更新update参数，如果是更新，则会更新issave标志（后续可增加其他更新），便于通过修改文件修正数据库
        /// 更新前请先执行保存，或者确认保存后执行更新操作时，只修改了xml配置文件的issave参数
        /// 更新参数name、type、size、code、issave
        /// *************************更新说明*************************
        /// </summary>
        /// <param name="dbConnection">数据库连接</param>
        /// <param name="dirXml">xml文件路径</param>
        /// <param name="update">是否执行更新</param>
        public static bool SaveXmlToDb(DbConnection dbConnection, string dirXml, bool update = false)
        {
            bool ret = false;

            //MySqlConnection NpgsqlConnection
            string connType = dbConnection.GetType().Name;

            /**********临时代码，把xml的数据写入数据库*************************************/
            //读取xml暂时保留
            Dictionary<string, SchemaDocument> schemaDict = SchemaServer.InitSchemaMapFromXMLFile(dirXml);

            //执行前提是file_param_main表数据正常
            List<string> sqls = new List<string>();
            string tableNameSource = "file_param_detail";   //真实数据表
            string tableName = "file_param_detail_tmp"; //临时表
            string tableNameTmp = "tmpTb_" + new Random().Next(100, 200);
            //string columns = "id, layer,file_name,parent_id,child_num,name,parent_idx, idx,type,size,device_type,loop_val,code,isalarm,alarmvalue,alarmname, memo,issave,get_time";
            string columns = "id, layer,file_name,parent_id,child_num,name,parent_idx, idx,type,size,device_type,loop_val,code, memo,issave,get_time";
            string sqlHead = "insert into " + tableName + "(" + columns + ")";
            string file_name_Pre = "";
            DbCommand dbCommand = dbConnection.CreateCommand();
            int order = 0;
            foreach (var key in schemaDict.Keys)
            {
                string values = " ";
                sqls.Clear();
                var doc = schemaDict[key];
                if (doc.file_name.Equals(file_name_Pre))
                {
                    continue;
                }
                order++;

                //执行前判断file_param_main是否存在该文件数据
                dbCommand.CommandText = "SELECT count(1) cnt FROM file_param_main WHERE file_name = '" + doc.file_name + "'";
                DbDataReader dataReader = dbCommand.ExecuteReader();
                int cnt = 0;
                while (dataReader.Read())
                {
                    cnt = dataReader.GetInt32(0);
                }
                dataReader.Close();

                //不存在时，插入数据
                if (cnt == 0)
                {
                    string sql = "insert into file_param_main(file_name, schema_name, msg_type, xml_type, memo, prefix_cols) " +
                        "values('" + doc.file_name + "','" + doc.name + "', '" + doc.msgType + "', '" + doc.xmlType.Replace(" ", "") + "','新增','" + doc.prefixCols + "')";
                    sqls.Add(sql);
                }
                else
                {
                    string sql = "update file_param_main set schema_name='" + doc.name + "', msg_type='" + doc.msgType +
                        "', xml_type='" + doc.xmlType.Replace(" ", "") + "', get_time = now(), memo='更新', prefix_cols='" + doc.prefixCols + "'" +
                        " WHERE file_name = '" + doc.file_name + "'";
                    sqls.Add(sql);
                }

                //循环得到插入语句
                file_name_Pre = doc.file_name;
                Dictionary<int, SchemaDictionary> dynamicParaDictionary = doc.fieldDictionary;
                int id = 0;
                for (int i = 0; i < dynamicParaDictionary.Count; i++)
                {
                    SchemaDictionary schema = dynamicParaDictionary[i];
                    values = values + (i > 0 ? "," : "") + GetValues(schema, doc.file_name, "-1", 0, ref id);
                }
                string sqlIn = sqlHead + " values" + values;

                sqls.Add("CREATE TABLE " + tableName + " as select * from " + tableNameSource + " where 1=2");
                sqls.Add("TRUNCATE TABLE " + tableName);
                if (order == 0)
                {
                    //if (connType.Equals("MySqlConnection"))
                    //{
                    //    sqls.Add("ALTER TABLE " + tableName + " MODIFY id INT AUTO_INCREMENT PRIMARY KEY");
                    //}
                    //else
                    //{
                    //    sqls.Add("ALTER TABLE " + tableName + " ADD CONSTRAINT " + tableName + "_pkey PRIMARY KEY (id)");
                    //}
                }

                sqls.Add(sqlIn);
                //一堆update语句
                sqls.Add("UPDATE " + tableName + " a SET main_id = (SELECT id FROM file_param_main b WHERE a.file_name = b.file_name)");

                sqls.Add("CREATE TABLE " + tableNameTmp + " AS SELECT main_id, MIN(id)-1 MID FROM " + tableName + " GROUP BY main_id");
                sqls.Add("UPDATE " + tableName + " a SET sort = (SELECT a.id - b.mid FROM " + tableNameTmp + " b WHERE a.main_id = b.main_id)");
                sqls.Add("DROP TABLE " + tableNameTmp);

                sqls.Add("CREATE TABLE " + tableNameTmp + " AS SELECT id, main_id, idx, parent_idx, child_num, layer FROM " + tableName);

                if (connType.Equals("MySqlConnection"))
                {
                    //mysql写法
                    sqls.Add("UPDATE " + tableName + " a SET parent_id = IFNULL((SELECT MAX(b.id) FROM " + tableNameTmp + " b WHERE a.layer-1 = b.layer AND a.main_id = b.main_id AND a.parent_idx = b.idx AND b.id < a.id ), -1)");
                }
                else
                {
                    //PostgreSQL写法
                    //达梦数据库写法
                    sqls.Add("UPDATE " + tableName + " a SET parent_id = NULLIF((SELECT MAX(b.id) FROM " + tableNameTmp + " b WHERE a.layer-1 = b.layer AND a.main_id = b.main_id AND a.parent_idx = b.idx AND b.id < a.id ), -1)");
                    sqls.Add("UPDATE " + tableName + " a SET parent_id = -1 where parent_id is null");
                }

                sqls.Add("DROP TABLE " + tableNameTmp);
                if (connType.Equals("MySqlConnection"))
                {
                    sqls.Add("UPDATE " + tableName + " SET loop_new = 1 WHERE (loop_val REGEXP '[A-Za-z]')=1");
                }
                else if ("DmConnection".Equals(connType))
                {
                    sqls.Add("UPDATE " + tableName + " SET loop_new = 1 WHERE regexp_count(loop_val,'[[:alpha:]]',1,'i') > 0");
                }
                else
                {
                    sqls.Add("UPDATE " + tableName + " SET loop_new = 1 WHERE loop_val ~ '^[0-9\\.]+$' = 'f' and loop_val != ''");
                }
                sqls.Add("UPDATE " + tableName + " SET issave = 0 WHERE name in('devid', 'functioncode', 'datalength', 'LLL', 'HHH')");

                //把把xml的数据写入数据库
                //sql语句一顿执行，必须全部执行通过才能进行后续操作
                bool rs = true;
                foreach (string sql in sqls)
                {
                    dbCommand.CommandText = sql;
                    try
                    {
                        dbCommand.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        if (sql.ToUpper().Contains(tableName.ToUpper()) &&
                            (e.Message.ToUpper().Contains(("already exists").ToUpper()) ||
                            (e.Message.Contains("file_param_detail_tmp") && e.Message.Contains("已经存在")) ||
                            (e.Message.Contains("file_param_detail_tmp") && e.Message.Contains("已存在"))
                            ))
                        {
                            continue;
                        }
                        else if (sql.ToUpper().Contains("PRIMARY KEY"))
                        {
                            continue;
                        }
                        else if (sql.ToUpper().Contains("DROP") && (e.Message.Contains("Unknown table") || e.Message.Contains("不存在")))
                        {
                            continue;
                        }

                        rs = false;
                        CommonUtil.mLogger.LogError("sql Error :" + ":" + sql + " " + e);

                        //确保临时表删除掉
                        try
                        {
                            dbCommand.CommandText = "DROP TABLE " + tableNameTmp;
                            dbCommand.ExecuteNonQuery();
                        }
                        catch { }
                        break;
                    }
                }

                //执行不通过，进行下一个文件
                if (!rs)
                {
                    CommonUtil.mLogger.LogError(order + " " + file_name_Pre + (update ? "更新" : "保存") + "失败！");
                    continue;
                }

                int addid = 0;
                if (update)
                {
                    //更新
                    //获取最小ID，用于ID匹配更新
                    dbCommand.CommandText = "SELECT MIN(id)-1 maxid FROM " + tableNameSource + "  where main_id = (SELECT DISTINCT main_id FROM " + tableName + ")";
                    dataReader = dbCommand.ExecuteReader();
                    while (dataReader.Read())
                    {
                        if (!dataReader.GetValue(0).ToString().Equals(""))
                        {
                            addid = dataReader.GetInt32(0);
                        }
                    }
                    dataReader.Close();
                }
                else
                {
                    //保存
                    //获取最大ID，用于insert
                    dbCommand.CommandText = "SELECT MAX(id) maxid FROM " + tableNameSource;
                    dataReader = dbCommand.ExecuteReader();
                    while (dataReader.Read())
                    {
                        if (!dataReader.GetValue(0).ToString().Equals(""))
                        {
                            addid = dataReader.GetInt32(0);
                        }
                    }
                    dataReader.Close();
                }


                //确保数据插入成功
                dbCommand.CommandText = "SELECT COUNT(1) cnt FROM " + tableName;
                dataReader = dbCommand.ExecuteReader();
                int countIns = 0;
                while (dataReader.Read())
                {
                    countIns = dataReader.GetInt32(0);
                }
                dataReader.Close();

                if (countIns > 0)
                {
                    try
                    {
                        dbCommand.CommandText = "update " + tableName + " set id = id + " + addid;
                        dbCommand.ExecuteNonQuery();

                        dbCommand.CommandText = "update " + tableName + " set parent_id = parent_id + " + addid + " where parent_id > 0";
                        dbCommand.ExecuteNonQuery();

                        //更新issave字段到正式表
                        if (update)
                        {
                            if (connType.Equals("MySqlConnection"))
                            {
                                //mysql写法
                                dbCommand.CommandText = "UPDATE " + tableNameSource + " b, " + tableName + " a SET b.issave = a.issave, b.type = a.type, b.name = a.name, b.size = a.size, b.code = a.code, b.get_time=a.get_time WHERE a.id = b.id AND a.main_id = b.main_id";
                            }
                            else
                            {
                                //PostgreSQL写法
                                //达梦数据库写法
                                dbCommand.CommandText = "update " + tableNameSource + " b set issave = a.issave, type = a.type, name = a.name, size = a.size, code = a.code, get_time=a.get_time from " + tableName + " a where a.id = b.id and a.main_id = b.main_id";
                            }
                            dbCommand.ExecuteNonQuery();
                        }
                        //保存，删除正式表数据，然后insert
                        else
                        {
                            dbCommand.CommandText = "delete from " + tableNameSource + " where main_id = (SELECT DISTINCT main_id FROM " + tableName + ")";
                            dbCommand.ExecuteNonQuery();

                            dbCommand.CommandText = "insert into " + tableNameSource + " select * from " + tableName;
                            dbCommand.ExecuteNonQuery();
                        }
                    }
                    catch (Exception e)
                    {
                        CommonUtil.mLogger.LogError("sql Error" + e.Message + ":" + dbCommand.CommandText);
                    }
                }

                ret = true;
                CommonUtil.mLogger.LogInformation(order + " " + file_name_Pre + (update ? "更新" : "保存") + "成功！");
            }
            /**********临时代码，把xml的数据写入数据库*************************************/

            return ret;
        }

        /// <summary>
        /// 读取值
        /// </summary>
        /// <param name="schObj"></param>
        /// <param name="fileName"></param>
        /// <param name="parentIdx"></param>
        /// <param name="layer"></param>
        /// <returns></returns>
        private static string GetValues(SchemaDictionary schema, string fileName, string parentIdx, int layer, ref int id)
        {
            id++;
            if (!schema.isNode)
            {
                return "(" + id + "," + layer + ",'" + fileName + "',-1, -1, '" + schema.name + "'," + parentIdx + "," + schema.index + ",'" + schema.type + "'," + schema.size +
                    ",'" + schema.deviceType + "', '" + schema.loop + "','" + schema.code + "'" + //"'," + (schema.isAlarm ? 1 : 0).ToString() +
                                                                                                  //",'" + schema.alarmValue + "','" + schema.alarmName + "','" + schema.memo + "', '" + (schema.isSave == null ? "1" : schema.isSave) + "', now())";
                    ",'" + schema.memo + "', '" + (schema.isSave == null ? "1" : schema.isSave) + "', now())";
            }
            else
            {
                Dictionary<int, SchemaDictionary> dynamicParaDictionaryIn = schema.fieldDictionary;
                string value = "(" + id + "," + layer + ",'" + fileName + "',-1, " + dynamicParaDictionaryIn.Count + ", '" + schema.name + "', " + parentIdx + "," + schema.index + ",'" +
                    schema.type + "'," + schema.size + ",'" + schema.deviceType + "', '" + schema.loop + "','" +
                    //schema.code + "'," + (schema.isAlarm ? 1 : 0).ToString() + ",'" + schema.alarmValue + "','" + schema.alarmName + "','" + schema.memo + "', '" + (schema.isSave == null ? "1" : schema.isSave) + "', now())";
                    schema.code + "','" + schema.memo + "', '" + (schema.isSave == null ? "1" : schema.isSave) + "', now())";

                int layerNext = layer + 1;
                for (int j = 0; j < dynamicParaDictionaryIn.Count; j++)
                {
                    value = value + "," + GetValues(dynamicParaDictionaryIn[j], fileName, schema.index.ToString(), layerNext, ref id);
                }
                return value;
            }
        }
    }
}
